﻿using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Reflection;
using Microsoft.EntityFrameworkCore;

namespace Devonline.AspNetCore;

/// <summary>
/// 数据库上下文扩展操作集合
/// </summary>
public static class DbContextExtensions
{
    /// <summary>
    /// 自动迁移
    /// </summary>
    /// <param name="context"></param>
    /// <returns></returns>
    public static async Task AutoMigrationAsync(this DbContext context)
    {
        if ((await context.Database.GetPendingMigrationsAsync()).Any())
        {
            await context.Database.MigrateAsync();
        }
    }

    /// <summary>
    /// 递归获取父级数据 
    /// </summary>
    /// <typeparam name="TEntitySet">业务数据类型</typeparam>
    /// <typeparam name="TKey">主键类型</typeparam>
    /// <param name="context">数据库上下文</param>
    /// <param name="id">当前业务数据编号</param>
    /// <returns></returns>
    public static async Task<List<TEntitySet>> GetParentsAsync<TEntitySet, TKey>(this DbContext context, TKey id) where TKey : IConvertible, IEquatable<TKey> where TEntitySet : class, IParent<TKey>, IEntitySetWithCreate<TKey>
    {
        var entitySets = new List<TEntitySet>();
        var entitySet = await context.Set<TEntitySet>().FindAsync(id);
        if (entitySet != null && entitySet.ParentId != null)
        {
            var parent = await context.Set<TEntitySet>().FirstOrDefaultAsync(x => x.State == DataState.Available && x.Id.Equals(entitySet.ParentId));
            if (parent != null)
            {
                entitySets.Add(parent);
                entitySets.AddRange(await context.GetParentsAsync<TEntitySet, TKey>(parent.Id));
            }
        }

        return entitySets;
    }
    /// <summary>
    /// 递归获取子级数据 
    /// </summary>
    /// <typeparam name="TEntitySet">业务数据类型</typeparam>
    /// <typeparam name="TKey">主键类型</typeparam>
    /// <param name="context">数据库上下文</param>
    /// <param name="id">当前业务数据编号</param>
    /// <returns></returns>
    public static async Task<List<TEntitySet>> GetChildrenAsync<TEntitySet, TKey>(this DbContext context, TKey id) where TKey : IConvertible, IEquatable<TKey> where TEntitySet : class, IParent<TKey>, IEntitySetWithCreate<TKey>
    {
        var entitySets = new List<TEntitySet>();
        if (id != null)
        {
            var children = await context.Set<TEntitySet>().Where(x => x.State == DataState.Available && x.ParentId != null && x.ParentId.Equals(id)).ToListAsync();
            if (children.IsNotNullOrEmpty())
            {
                entitySets.AddRange(children);
                foreach (var child in children)
                {
                    entitySets.AddRange(await context.GetChildrenAsync<TEntitySet, TKey>(child.Id));
                }
            }
        }

        return entitySets;
    }

    /// <summary>
    /// 递归获取父级数据, 字符串作为主键的默认实现
    /// </summary>
    /// <typeparam name="TEntitySet">业务数据类型</typeparam>
    /// <param name="context">数据库上下文</param>
    /// <param name="id">当前业务数据编号</param>
    /// <returns></returns>
    public static async Task<List<TEntitySet>> GetParentsAsync<TEntitySet>(this DbContext context, string id) where TEntitySet : class, IParent<string>, IEntitySetWithCreate<string> => await context.GetParentsAsync<TEntitySet, string>(id);
    /// <summary>
    /// 递归获取子级数据, 字符串作为主键的默认实现
    /// </summary>
    /// <typeparam name="TEntitySet">业务数据类型</typeparam>
    /// <param name="context">数据库上下文</param>
    /// <param name="id">当前业务数据编号</param>
    /// <returns></returns>
    public static async Task<List<TEntitySet>> GetChildrenAsync<TEntitySet>(this DbContext context, string id) where TEntitySet : class, IParent<string>, IEntitySetWithCreate<string> => await context.GetChildrenAsync<TEntitySet, string>(id);

    /// <summary>
    /// 使用 insert into table (columns) values (values), (values)... 的语法批量写入数据
    /// 在单次写入数量或字符限制大小某一个达到限制值时即提交一次
    /// </summary>
    /// <typeparam name="TEntitySet">待写入的数据类型</typeparam>
    /// <typeparam name="TKey">主键类型</typeparam>
    /// <param name="context">数据库上下文</param>
    /// <param name="data">待写入数据</param>
    /// <param name="limit">单次写入限制数量, 默认 1000, 这个值由数据库链接决定</param>
    /// <param name="maxSize">单次写入的字符大小限制, 默认 1M 字符</param>
    /// <param name="separators">字段引用符号, 针对字段名是 数据库关键字的情况下, 每种数据库有不同的引用表示方式, 如 SQLServer: [], MySQL:` , 默认无</param>
    /// <returns></returns>
    /// <exception cref="Exception"></exception>
    public static async Task<int> BulkInsertAsync<TEntitySet, TKey>(this DbContext context, IEnumerable<TEntitySet> data, int limit = AppSettings.UNIT_THOUSAND, int maxSize = AppSettings.UNIT_MEGA, params string[] separators) where TEntitySet : class, IEntitySet<TKey>
    {
        if (data == null || !data.Any())
        {
            return 0;
        }

        var type = typeof(TEntitySet);
        var propertyInfos = type.GetProperties().Where(x => x.HasAttribute<ColumnAttribute>() && !x.HasAttribute<NotMappedAttribute>());
        var separatorLeft = string.Empty;
        var separatorRight = string.Empty;
        if (separators.Length >= 1)
        {
            separatorLeft = separators[0];
            separatorRight = separators.Length >= 2 ? separators[1] : separators[0];
        }

        var sqlInsert = $"INSERT INTO {type.GetTableName()} ({string.Join(AppSettings.DEFAULT_SPLITER_STRING, propertyInfos.Select(p => separatorLeft + p.GetColumnName() + separatorRight))}) VALUES ";
        var values = new List<string>();
        var index = 0;
        var total = 0;
        var sql = string.Empty;
        foreach (var entity in data)
        {
            var sqls = new List<string>();
            foreach (var propertyInfo in propertyInfos)
            {
                sqls.Add(entity.GetSqlStringValue<TEntitySet, TKey>(propertyInfo) ?? "NULL");
            }

            index++;
            values.Add($"({string.Join(AppSettings.DEFAULT_SPLITER_STRING, sqls)})");
            if (index >= limit || values.Sum(x => x.Length) >= maxSize)
            {
                sql = sqlInsert + string.Join(AppSettings.DEFAULT_SPLITER_STRING, values);
                total += await context.Database.ExecuteSqlRawAsync(sql);
                values.Clear();
                sqls.Clear();
                index = 0;
            }
        }

        sql = sqlInsert + string.Join(AppSettings.DEFAULT_SPLITER_STRING, values) + AppSettings.CHAR_SEMICOLON;
        total += await context.Database.ExecuteSqlRawAsync(sql);
        return total;
    }
    /// <summary>
    /// 使用 insert into table (columns) values (values), (values)... 的语法批量写入数据
    /// 字符串作为主键的默认实现
    /// </summary>
    /// <typeparam name="TEntitySet">待写入的数据类型</typeparam>
    /// <param name="context">数据库上下文</param>
    /// <param name="data">待写入数据</param>
    /// <param name="limit">单次写入限制数量, 默认 1000, 这个值由数据库链接决定</param>
    /// <param name="maxSize">单次写入的字符大小限制, 默认 1M 字符</param>
    /// <param name="separators">字段引用符号, 针对字段名是 数据库关键字的情况下, 每种数据库有不同的引用表示方式, 如 SQLServer: [], MySQL:` , 默认无</param>
    /// <returns></returns>
    public static async Task<int> BulkInsertAsync<TEntitySet>(this DbContext context, IEnumerable<TEntitySet> data, int limit = AppSettings.UNIT_HUNDRED, int maxSize = AppSettings.UNIT_MEGA, params string[] separators) where TEntitySet : class, IEntitySet<string> => await context.BulkInsertAsync<TEntitySet, string>(data, limit, maxSize, separators);
    /// <summary>
    /// 使用 insert into table (columns) values (values), (values)... 的语法批量写入数据
    /// 在单次写入数量或字符限制大小某一个达到限制值时即提交一次
    /// 本方法根据已知数据库类型构建 sql 语句完成批量写入
    /// </summary>
    /// <typeparam name="TEntitySet">待写入的数据类型</typeparam>
    /// <param name="context">数据库上下文</param>
    /// <param name="data">待写入数据</param>
    /// <param name="databaseType">数据库类型, 默认使用 PostgreSQL 数据库</param>
    /// <param name="limit">单次写入限制数量, 默认 1000, 这个值由数据库链接决定</param>
    /// <param name="maxSize">单次写入的字符大小限制, 默认 1M 字符</param>
    /// <returns></returns>
    public static async Task<int> BulkInsertAsync<TEntitySet>(this DbContext context, IEnumerable<TEntitySet> data, DatabaseType databaseType = DatabaseType.PostgreSQL, int limit = AppSettings.UNIT_THOUSAND, int maxSize = AppSettings.UNIT_MEGA) where TEntitySet : class, IEntitySet<string> => await context.BulkInsertAsync(data, limit, maxSize, databaseType.GetDatabaseSeparator());

    /// <summary>
    /// 使用 update table set column1=value1,column2=value ... 的语法批量更新数据
    /// 在单次更新数量或字符限制大小某一个达到限制值时即提交一次
    /// </summary>
    /// <typeparam name="TEntitySet">待更新的数据类型</typeparam>
    /// <typeparam name="TKey">主键类型</typeparam>
    /// <param name="context">数据库上下文</param>
    /// <param name="data">待更新数据</param>
    /// <param name="limit">单次更新限制数量, 默认 1000, 这个值由数据库链接决定</param>
    /// <param name="maxSize">单次更新的字符大小限制, 默认 1M 字符</param>
    /// <param name="separators">字段引用符号, 针对字段名是 数据库关键字的情况下, 每种数据库有不同的引用表示方式, 如 SQLServer: [], MySQL:` , 默认无</param>
    /// <returns></returns>
    /// <exception cref="Exception"></exception>
    public static async Task<int> BulkUpdateAsync<TEntitySet, TKey>(this DbContext context, IEnumerable<TEntitySet> data, int limit = AppSettings.UNIT_THOUSAND, int maxSize = AppSettings.UNIT_MEGA, params string[] separators) where TEntitySet : class, IEntitySet<TKey>
    {
        if (data == null || !data.Any())
        {
            return 0;
        }

        var type = typeof(TEntitySet);
        var propertyInfos = type.GetProperties().Where(x => x.HasAttribute<ColumnAttribute>() && !x.HasAttribute<NotMappedAttribute>());
        var separatorLeft = string.Empty;
        var separatorRight = string.Empty;
        if (separators.Length >= 1)
        {
            separatorLeft = separators[0];
            separatorRight = separators.Length >= 2 ? separators[1] : separators[0];
        }

        var sqlUpdate = $"UPDATE {type.GetTableName()} SET ";
        var values = new List<string>();
        var index = 0;
        var total = 0;
        var sql = string.Empty;
        var idField = nameof(IEntitySet<TKey>.Id);
        foreach (var entity in data)
        {
            var idCondition = string.Empty;
            var sqls = new List<string>();
            foreach (var propertyInfo in propertyInfos)
            {
                var stringValue = entity.GetSqlStringValue<TEntitySet, TKey>(propertyInfo) ?? "NULL";
                var fieldExpression = separatorLeft + propertyInfo.Name + separatorRight + AppSettings.CHAR_EQUAL + stringValue;
                if (propertyInfo.Name == idField)
                {
                    idCondition = fieldExpression;
                }
                else
                {
                    sqls.Add(fieldExpression);
                }
            }

            index++;
            values.Add(sqlUpdate + string.Join(AppSettings.DEFAULT_SPLITER_STRING, sqls) + " WHERE " + idCondition);
            if (index >= limit || values.Sum(x => x.Length) >= maxSize)
            {
                sql = string.Join(AppSettings.CHAR_SEMICOLON, values) + AppSettings.CHAR_SEMICOLON;
                total += await context.Database.ExecuteSqlRawAsync(sql);
                values.Clear();
                sqls.Clear();
                index = 0;
            }
        }

        sql = string.Join(AppSettings.CHAR_SEMICOLON, values) + AppSettings.CHAR_SEMICOLON;
        total += await context.Database.ExecuteSqlRawAsync(sql);
        return total;
    }
    /// <summary>
    /// 使用 update table set column1=value1,column2=value ... 的语法批量更新数据
    /// 字符串作为主键的默认实现
    /// </summary>
    /// <typeparam name="TEntitySet">待更新的数据类型</typeparam>
    /// <param name="context">数据库上下文</param>
    /// <param name="data">待更新数据</param>
    /// <param name="limit">单次更新限制数量, 默认 1000, 这个值由数据库链接决定</param>
    /// <param name="maxSize">单次更新的字符大小限制, 默认 1M 字符</param>
    /// <param name="separators">字段引用符号, 针对字段名是 数据库关键字的情况下, 每种数据库有不同的引用表示方式, 如 SQLServer: [], MySQL:` , 默认无</param>
    /// <returns></returns>
    public static async Task<int> BulkUpdateAsync<TEntitySet>(this DbContext context, IEnumerable<TEntitySet> data, int limit = AppSettings.UNIT_HUNDRED, int maxSize = AppSettings.UNIT_MEGA, params string[] separators) where TEntitySet : class, IEntitySet<string> => await context.BulkUpdateAsync<TEntitySet, string>(data, limit, maxSize, separators);
    /// <summary>
    /// 使用 update table set column1=value1,column2=value ... 的语法批量更新数据
    /// 在单次更新数量或字符限制大小某一个达到限制值时即提交一次
    /// 本方法根据已知数据库类型构建 sql 语句完成批量更新
    /// </summary>
    /// <typeparam name="TEntitySet">待写入的数据类型</typeparam>
    /// <param name="context">数据库上下文</param>
    /// <param name="data">待写入数据</param>
    /// <param name="databaseType">数据库类型, 默认使用 PostgreSQL 数据库</param>
    /// <param name="limit">单次写入限制数量, 默认 1000, 这个值由数据库链接决定</param>
    /// <param name="maxSize">单次写入的字符大小限制, 默认 1M 字符</param>
    /// <returns></returns>
    public static async Task<int> BulkUpdateAsync<TEntitySet>(this DbContext context, IEnumerable<TEntitySet> data, DatabaseType databaseType = DatabaseType.PostgreSQL, int limit = AppSettings.UNIT_HUNDRED, int maxSize = AppSettings.UNIT_MEGA) where TEntitySet : class, IEntitySet<string> => await context.BulkUpdateAsync(data, limit, maxSize, databaseType.GetDatabaseSeparator());

    /// <summary>
    /// 获取数据库字段名分隔符
    /// </summary>
    /// <param name="databaseType"></param>
    /// <returns></returns>
    /// <exception cref="Exception"></exception>
    public static string[] GetDatabaseSeparator(this DatabaseType databaseType)
    {
        string? left;
        string? right;
        switch (databaseType)
        {
            case DatabaseType.SQLServer:
                left = "[";
                right = "]";
                break;
            case DatabaseType.PostgreSQL:
            case DatabaseType.Oracle:
                left = "\"";
                right = "\"";
                break;
            case DatabaseType.MySQL:
                left = "`";
                right = "`";
                break;
            default:
                throw new Exception("不支持的数据库类型!");
        }

        return new string[] { right, left };
    }
    /// <summary>
    /// 获取制定类型的字段构造 sql 语句时的字符串表达式
    /// </summary>
    /// <typeparam name="TEntitySet"></typeparam>
    /// <typeparam name="TKey"></typeparam>
    /// <param name="entitySet"></param>
    /// <param name="propertyInfo"></param>
    /// <exception cref="Exception"></exception>
    public static string? GetSqlStringValue<TEntitySet, TKey>(this TEntitySet entitySet, PropertyInfo propertyInfo) where TEntitySet : class, IEntitySet<TKey>
    {
        var value = propertyInfo.GetValue(entitySet);
        var propertyType = propertyInfo.PropertyType.GetCoreType();
        if (value == null && (propertyInfo.PropertyType.HasAttribute<RequiredAttribute>() || propertyType != typeof(string) && !propertyInfo.PropertyType.IsNullable()))
        {
            throw new Exception($"The type of {typeof(TEntitySet).GetDisplayName()} has a required property: {propertyInfo.Name} for object id: {entitySet.Id}");
        }

        if (value is null)
        {
            return "NULL";
        }

        if (propertyType.IsEnum)
        {
            var columnType = propertyInfo.GetAttributeValue<ColumnAttribute, string>(nameof(ColumnAttribute.TypeName));
            if (!string.IsNullOrWhiteSpace(columnType) && columnType.Contains("VARCHAR"))
            {
                return AppSettings.CHAR_QUOTE + Enum.Format(propertyType, value, "F") + AppSettings.CHAR_QUOTE;
            }
            else
            {
                return Enum.Format(propertyType, value, "D");
            }
        }

        switch (value)
        {
            case bool boolValue: return boolValue ? "1" : "0";
            case DateTime datetime: return AppSettings.CHAR_QUOTE + datetime.ToString(AppSettings.DEFAULT_DATETIME_FORMAT) + AppSettings.CHAR_QUOTE;
            case DateTimeOffset datetimeOffset: return AppSettings.CHAR_QUOTE + datetimeOffset.ToString(AppSettings.DEFAULT_ISO_DATETIME_WITH_TIMEZONE_FORMAT) + AppSettings.CHAR_QUOTE;
            default:
                break;
        }

        return Type.GetTypeCode(propertyType) switch
        {
            TypeCode.SByte or TypeCode.Byte or TypeCode.Int16 or TypeCode.UInt16 or TypeCode.Int32 or TypeCode.UInt32 or TypeCode.Int64 or TypeCode.UInt64 or TypeCode.Single or TypeCode.Double or TypeCode.Decimal => value.ToString(),
            TypeCode.String or TypeCode.Char => AppSettings.CHAR_QUOTE + value.ToString() + AppSettings.CHAR_QUOTE,
            _ => throw new Exception("The type of value can not convert to string!")
        };
    }
}